/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.seagate.app.manager;

import com.seagate.app.bean.BoBean;
import com.seagate.app.bean.RoomBean;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import com.seagate.util.db.OracleConnectionPool;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author 452530
 */
public class RoomManager {
  public Map loadAllRoomPercentCap(){
      Map roomCap = new HashMap();
       String sql = "SELECT  B.ROOM_SHELF_NO,SUM(B.QTY)/A.CAPACITY NUM   FROM AMA_STDPART_BO B LEFT JOIN AMA_STDPART_ROOM A  ON A.SHELF_NO=B.ROOM_SHELF_NO GROUP BY B.ROOM_SHELF_NO, A.CAPACITY";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
              roomCap.put(rset.getString("ROOM_SHELF_NO"), rset.getString("NUM"));
 
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
      return roomCap;
  }
  public List loadAllRoomList(){
      List room = new ArrayList();
       String sql = "SELECT  *   FROM   AMA_STDPART_ROOM WHERE SHELF_NO not like 'F%'  ";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
                RoomBean rm =new RoomBean();
              rm.setShelf_no(rset.getString("SHELF_NO"));
              rm.setProductname(rset.getString("PRODUCTNAME"));
              rm.setCapacity(rset.getInt("CAPACITY"));
              rm.setTga_type(rset.getString("TGA_TYPE"));
              rm.setBond_pad(rset.getInt("BOND_PAD"));

              room.add(rm);

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
      return room;
  }
    public List loadAllRoomShelfBo(String shelfno){
      List bolist = new ArrayList();
       String sql = "SELECT  BONAME,TSR,DATE_RECEIVE,TAB,HGA_PARTNUM, ROOM_SHELF_NO, QTY    FROM AMA_STDPART_BO WHERE ROOM_SHELF_NO='"+shelfno+"' ORDER BY BONAME";
       System.out.println(sql);
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
            BoBean bo = new BoBean();
                bo.setBoname(rset.getString("BONAME"));
                bo.setTsr(rset.getString("TSR"));
                bo.setDate_receive(rset.getString("DATE_RECEIVE"));
                bo.setTab(rset.getString("TAB"));
                bo.setHga_partnum(rset.getString("HGA_PARTNUM"));
                bo.setRoom_shelf_no(rset.getString("ROOM_SHELF_NO"));
                bo.setQty(rset.getString("QTY"));
                bolist.add(bo);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
      return bolist;
  }
public BoBean getRoomShelfBo(String shelfno,String hgabo){
       BoBean bo = new BoBean();
       String sql = "SELECT  BONAME,TSR,DATE_RECEIVE,TAB,HGA_PARTNUM, ROOM_SHELF_NO, QTY FROM AMA_STDPART_BO WHERE ROOM_SHELF_NO='"+shelfno+"' AND BONAME='"+hgabo+"' ORDER BY BONAME";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {

                bo.setBoname(rset.getString("BONAME"));
                bo.setTsr(rset.getString("TSR"));
                bo.setDate_receive(rset.getString("DATE_RECEIVE"));
                bo.setTab(rset.getString("TAB"));
                bo.setHga_partnum(rset.getString("HGA_PARTNUM"));
                bo.setRoom_shelf_no(rset.getString("ROOM_SHELF_NO"));
                bo.setQty(rset.getString("QTY"));
                return bo;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
      return null;
  }
public BoBean getRoomShelfBoByHgaBo(String hgabo){
       BoBean bo = new BoBean();
       String sql = "SELECT  BONAME,TSR,DATE_RECEIVE,TAB,HGA_PARTNUM, ROOM_SHELF_NO, QTY FROM AMA_STDPART_BO WHERE BONAME='"+hgabo+"' AND ROOM_SHELF_NO not like 'F%'  ORDER BY BONAME";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {

                bo.setBoname(rset.getString("BONAME"));
                bo.setTsr(rset.getString("TSR"));
                bo.setDate_receive(rset.getString("DATE_RECEIVE"));
                bo.setTab(rset.getString("TAB"));
                bo.setHga_partnum(rset.getString("HGA_PARTNUM"));
                bo.setRoom_shelf_no(rset.getString("ROOM_SHELF_NO"));
                bo.setQty(rset.getString("QTY"));
                return bo;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
      return null;
  }
public BoBean getBoEts(String hgabo){
       BoBean bo = new BoBean();
       String sql = "SELECT HGA_BO,CL_HGA_ET_TSR,CL_HGA_PN,TAB FROM REPORT.VIW_HISMESRPT_USER WHERE HGA_BO ='"+hgabo+"' ";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {

                bo.setBoname(rset.getString("HGA_BO"));
                bo.setTsr(rset.getString("CL_HGA_ET_TSR"));
                bo.setTab(rset.getString("TAB"));
                bo.setHga_partnum(rset.getString("CL_HGA_PN"));                
                return bo;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
      return null;
  }
  public double getRoomPercentCapByShelf(String shelfno){
       
       String sql = "SELECT  SUM(B.QTY)/A.CAPACITY NUM   FROM AMA_STDPART_BO B LEFT JOIN AMA_STDPART_ROOM A  ON A.SHELF_NO=B.ROOM_SHELF_NO WHERE B.ROOM_SHELF_NO='"+shelfno+"' GROUP BY B.ROOM_SHELF_NO, A.CAPACITY";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
              return rset.getDouble("NUM");
//                bo.setEvent_date(rset.getString(1));
//                bo.setEquip_id(rset.getString(1));
//                bo.setTray_id(rset.getString(1));
//                bo.setTray_slot(rset.getInt(1));
//                bo.setOp_num(rset.getString(1));
//                bo.setHd_num(rset.getString(1));
//                bo.setSetup_file(rset.getString(1));
//                bo.setWork_order(rset.getString(1));
//                bo.setPart_num(rset.getString(1));
//                bo.setStart_date(rset.getString(1));
//                bo.setEvent_status(rset.getString(1));
//                bo.setMax_adj(rset.getInt(1));
//                bo.setPtarget(rset.getDouble(1));
//                bo.setPsa_in(rset.getDouble(1));
//                bo.setPsa_out(rset.getDouble(1));
//                bo.setRsa_in(rset.getDouble(1));
//                bo.setRsa_out(rset.getDouble(1));
//                bo.setBuild_intent_number(rset.getString(1));
//                bolist.add(bo);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
      return 0;
  }

  public int getBoQtyByShelf(String shelfno){

       String sql = "SELECT  SUM(QTY) NUM   FROM AMA_STDPART_BO   WHERE  ROOM_SHELF_NO='"+shelfno+"' GROUP BY ROOM_SHELF_NO";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {
              return rset.getInt("NUM");
 
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
      return 0;
  }
  public RoomBean loadRoomShelfInfo(String shelfno){
      RoomBean roomCap = new RoomBean();
       String sql = "SELECT  SHELF_NO,PRODUCTNAME,TGA_TYPE,CAPACITY,BOND_PAD   FROM  AMA_STDPART_ROOM  WHERE SHELF_NO='"+shelfno+"'";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sql);
            while (rset.next() == true) {

             roomCap.setShelf_no(rset.getString("SHELF_NO"));
             roomCap.setProductname(rset.getString("PRODUCTNAME"));
             roomCap.setTga_type(rset.getString("TGA_TYPE"));
             roomCap.setCapacity(rset.getInt("CAPACITY"));
             roomCap.setBond_pad(rset.getInt("BOND_PAD"));
             return roomCap;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rset.close();
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }
      return null;
  }

      public boolean updateShelf(String shelfno, String productname, String tgatype,String bondpad) {
        boolean result = false;
        String sql = "";
        sql = "update AMA_STDPART_ROOM set  "
                + " PRODUCTNAME='" + productname + "',"
                + " TGA_TYPE='" + tgatype + "',"
                + " BOND_PAD='" + bondpad + "'"
                + " where SHELF_NO = '" + shelfno + "'";

        Connection conn = null;
        Statement stmt = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();

            stmt.executeUpdate(sql);
            result = true;

        } catch (SQLException e) {
            System.err.println("sql = " + sql);
            System.err.println("code = " + e.getLocalizedMessage());
            result = false;
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return result;
    }
  public boolean updateShelfBo(String shelfno, String hgabo, String qty,String tab,String tsr,String partnum) {
        boolean result = false;
        String sql = "";
        sql = "update AMA_STDPART_BO set  "
               // + " BONAME='" + hgabo + "',"
                + " TAB='" + tab + "',"
                + " TSR='" + tsr + "',"
                + " HGA_PARTNUM='" + partnum + "',"
                + " QTY='" + qty + "'"

                + " where ROOM_SHELF_NO = '" + shelfno + "' AND BONAME='" + hgabo + "'" ;
System.out.println("sql updateShelfBo="+sql);
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();

            stmt.executeUpdate(sql);
            result = true;

        } catch (SQLException e) {
            System.err.println("sql = " + sql);
            System.err.println("code = " + e.getLocalizedMessage());
            result = false;
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return result;
    }
  public boolean deleteShelfBo(String shelfno, String hgabo) {
        boolean result = false;
        String sql = "";
        sql = "delete AMA_STDPART_BO WHERE  "
                + " BONAME='" + hgabo + "' AND "
                + " ROOM_SHELF_NO='" + shelfno + "' ";

        Connection conn = null;
        Statement stmt = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();

            stmt.executeUpdate(sql);
            result = true;

        } catch (SQLException e) {
            System.err.println("sql = " + sql);
            System.err.println("code = " + e.getLocalizedMessage());
            result = false;
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return result;
    }
  public boolean emptyBo(String shelfno) {
        boolean result = false;
        String sql = "";
        sql = "delete AMA_STDPART_BO WHERE  "
                + " ROOM_SHELF_NO='F" + shelfno + "' OR "
                + " ROOM_SHELF_NO='" + shelfno + "' ";

        Connection conn = null;
        Statement stmt = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();

            stmt.executeUpdate(sql);
            result = true;

        } catch (SQLException e) {
            System.err.println("sql = " + sql);
            System.err.println("code = " + e.getLocalizedMessage());
            result = false;
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return result;
    }
   public boolean addRejectLog( String hgabo, String rejectnum,String tab,String tsr,String partnum,String gtatype,String bondpad,String gid) {
        boolean result = false;
        String sql = "";


         sql = "insert into AMA_STDPART_REJ_LOG (BONAME,TSR,TAB,HGA_PARTNUM,REJECT_NUM,BOND_PAD,TGA_TYPE,GID) "
                    + " values ('" + hgabo + "','"+tsr+"','"+tab+"','"+partnum+"','"+rejectnum+"','"+bondpad+"','"+gtatype+"','"+gid+"')";

        Connection conn = null;
        Statement stmt = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();

            stmt.executeUpdate(sql);
            result = true;

        } catch (SQLException e) {
            System.err.println("sql = " + sql);
            System.err.println("code = " + e.getLocalizedMessage());
            result = false;
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return result;
    }
  public boolean addShelfBo(String shelf,String hgabo,String tsr,String partnum,String tab,String qty) {
        boolean result = false;
        String sql = "";
        sql = "insert into AMA_STDPART_BO (BONAME,TSR,TAB,HGA_PARTNUM,QTY,ROOM_SHELF_NO) "
                    + " values ('" + hgabo + "','"+tsr+"','"+tab+"','"+partnum+"','"+qty+"','"+shelf+"')";
        System.out.println("sql addShelfBo ="+sql);
        Connection conn = null;
        Statement stmt = null;

        try {
            conn = OracleConnectionPool.getConnection();
            stmt = conn.createStatement();

            stmt.executeUpdate(sql);
            result = true;

        } catch (SQLException e) {
            System.err.println("sql = " + sql);
            System.err.println("code = " + e.getLocalizedMessage());
            result = false;
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException ex) {
            }
        }

        return result;
    }
}
